﻿-- CREATE TRIGGER trgchange_attachment_in_po
-- AFTER INSERT or UPDATE or Delete
-- ON ir_attachment
-- FOR EACH ROW
-- EXECUTE PROCEDURE trgfnattachemnt_updatedata_po_attached_insertupdate();

CREATE OR REPLACE FUNCTION trgfnattachemnt_updatedata_po_attached_insertupdate()
 RETURNS trigger AS
	$BODY$
		Declare r_id integer;
			r_model varchar(120); --Name of model
			
			po_quotation boolean := False; -- Check quoation data attached 
			po_roa_comparison boolean := False;
			po_contract boolean := False;
		Begin
--Check What the Attached change		
			if TG_OP='DELETE' then
				begin
					r_id=old.res_id;
					r_model=old.res_model;
					if r_model='purchase.order' then
						if old.po_quotation_attached=True then
							po_quotation=True;
						end if;
						if old.po_roa_comparison_attached=True then
							po_roa_comparison=True;
						end if;
						if old.po_contract_attached=True then
							po_contract=True;
						end if;
					end if;
				end;
			end if;
			
			if TG_OP='INSERT' THEN
				begin
					r_id=new.res_id;
					r_model=new.res_model;
					if r_model='purchase.order' then
						if new.po_quotation_attached=True then
							po_quotation=True;
						end if;
						if new.po_roa_comparison_attached=True then
							po_roa_comparison=True;
						end if;
						if new.po_contract_attached=True then
							po_contract=True;
						end if;
					end if;
				end;
			end if;
			
			if TG_OP='UPDATE' THEN
				begin
					r_id=new.res_id;
					r_model=new.res_model;
					if r_model='purchase.order' then
						if new.po_quotation_attached<>old.po_quotation_attached or (new.po_quotation_attached is not null and old.po_quotation_attached is null) then
							po_quotation = True;
						end if;
						
						if new.po_roa_comparison_attached<>old.po_roa_comparison_attached or (new.po_roa_comparison_attached is not null and old.po_roa_comparison_attached is null) then
							po_roa_comparison = True;
						end if;
						
						if new.po_contract_attached<>old.po_contract_attached or (new.po_contract_attached is not null or old.po_contract_attached is null) then
							po_contract = True;
						end if;
					end if;
				end;
			end if;

			if r_model='purchase.order' and r_id is not null Then
				if po_quotation then --Update status Quotation Attached
					Update 
						purchase_order 
					Set
						quotation_attached=(Select 
								case when sum(case when po_quotation_attached is null or po_quotation_attached=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='purchase.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;
				if po_roa_comparison then --Update status ROA Comparison Sheet
					Update 
						purchase_order 
					Set
						roa_comaprison_attached=(Select 
								case when sum(case when po_roa_comparison_attached is null or po_roa_comparison_attached=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='purchase.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;
				if po_contract then --Update status Contract
					Update 
						purchase_order 
					Set
						contract_attached=(Select 
								case when sum(case when po_contract_attached is null or po_contract_attached=False then 0 else 1 end)>0 then True else false end
							from ir_attachment
							where 
								res_model='purchase.order' and res_id=r_id
							group by
								res_model,res_id)
						where
							id=r_id;
					
				end if;
			end if;
			
		if TG_OP='DELETE' then
			Return old;
		else
			return new;
		end if;
		end;
	$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;


